WAND: A CASE Tool for Data Warehouse Design

The statistic reports about data warehouse project failures state that a major cause lies in the absence of a structured design methodology. In this direction, our research is aimed at defining the basic steps required for a correct design. The goal of this demonstration is to present the main features of WAND, the prototype CASE tool we have implemented to support our methodology. WAND assists the designer in structuring a data mart, carries out conceptual design in a semi-automatic fashion, allows for a core workload to be defined on the conceptual scheme and carries out logical design to produce the data mart scheme. 1 . Motivation and overview Building a data warehouse (DW) for an enterprise is a huge and complex task, which requires an accurate planning aimed at devising satisfactory answers to organizational and architectural questions. There is substantial agreement on the fact that, when planning a DW, a bottom-up approach should be followed: the data mart playing the most strategic role for the enterprise should be identified and prototyped first in order to convince the final users of the potential benefits; other data marts are built progressively, to be finally integrated bottom-up into the global warehouse. Within this process, the phase of data mart design sometimes seems to be given relatively small importance. On the other hand, the statistic reports related to DW project failures state that a major cause lies in the absence of a global view of the design process: in other terms, in the absence of a structured design methodology [6]. We believe that a methodological framework for design is an essential requirement to ensure the success of complex projects. In this direction, our research is aimed at defining the basic steps required for a correct design. In particular, we considered the problem of conceptual design from the operational database as well as some relevant issues related to logical design. It is well-known among software engineers that devising a design methodology is almost useless, if no CASE tool to support it is provided. The goal of this demonstration is to present the main features of WAND ( Wa rehouse I ntegrated Designer), the prototype CASE tool we have implemented to support our methodology. WAND assists the designer in structuring a data mart; it carries out conceptual design in a semi-automatic fashion starting from the logical scheme of the operational database (read via ODBC), allows for a core workload to be defined on the conceptual scheme and carries out logical design to produce the data mart scheme. Both the Table I. The six phases in our DW design methodology. Step Input Output Involves Analysis of the operational database existing documentation (reconciled) database scheme designer; managers of the information system Requirement specification database scheme facts; preliminary workload designer; final users Conceptual design database scheme; facts; preliminary workload conceptual scheme designer; final users Workload refinement, conc. scheme validation conceptual scheme; preliminary workload validated conceptual scheme; workload designer; final users Logical design conceptual scheme; data volume; workload logical scheme designer Physical design logical scheme; target DBMS; workload physical scheme designer